<?php
namespace app\admin\model;
use think\Model;
use think\Db;

use think\facade\Config;
/**
 * 报表模型
 * @package app\admin\model
 */
class BusOrderReport extends Model
{

	//查询记录
	//CALL p_GetChannelReport('2019-06-13','2019-06-14')
// CALL p_GetChannelReportByCid('2019-06-13','2019-06-14',2995)
	public function findReport($wheret,$cha = '')
	{
		$dataall = [];

		$wheret = str_replace("-","",$wheret);

		$user_type = session('user_type');
		$user_id  = session('user_id');

		if($user_type == 'clerk'){
			$channelList = Db::table('bus_user_and_channel')
			->field('bus_channel.channel_id,bus_channel.company_name')
			->join('bus_channel','bus_channel.channel_id=bus_user_and_channel.channel_id')
			->where('bus_user_and_channel.user_id',$user_id )
			->select();

		}elseif($user_type == 'admin' && Db::table('adm_user')->where('id',$user_id )->value('role_id') == '54' ){
			$channelList = Db::table('bus_user_and_channel')
			->field('bus_channel.channel_id,bus_channel.company_name')
			->join('bus_channel','bus_channel.channel_id=bus_user_and_channel.channel_id')
			->select();

		}elseif($user_type == 'admin'){

			if($cha){
				$channelList = Db::table('bus_channel')->field('channel_id,company_name')
				->where('channel_id',$cha)
				->select();;
			}else{
				$channelList = Db::table('bus_channel')->field('channel_id,company_name')
				// ->where('status',1)
				->select();
			}


		}


		$config = Config::get('ZD_REDIS_CONFIG');

		$redis = redisCurl($config['IP'],$config['PORT'],$config['PASSWORD'],6);

		//分运营商
		$operators = [
			['name' => '移动' , 'host' => '_10086']
			,['name' => '联通' , 'host' => '_10010']
			,['name' => '电信' , 'host' => '_10000']
			,['name' => '三网' , 'host' => '_90001']
			,['name' => '三网' , 'host' => '']
		];

		// 汇总
		$allone['company_name']    = '汇总';
        $allone['cid']             = 0;
        $allone['sms_all']         = 0;
        $allone['sms_success']     = 0;
        $allone['sms_faild']       = 0;
        $allone['sms_sending']     = 0;
        $allone['amt_sms_success'] = 0;

		foreach ($channelList as $key => $value) {
			$chaneelsms = [];
			//单个渠道汇总
			$chaneelsms['company_name']    = $value['channel_id'] . '/' . $value['company_name'];
	        $chaneelsms['cid']             = $value['channel_id'];
	        $chaneelsms['sms_all']         = 0;
	        $chaneelsms['sms_success']     = 0;
	        $chaneelsms['sms_faild']       = 0;
	        $chaneelsms['sms_sending']     = 0;
	        $chaneelsms['amt_sms_success'] = 0;
	        $chaneelsms['operator'] = '汇总';
	        $chaneelsms['data'] = [];

			for ($i=0; $i < count($operators); $i++) { 
				$dataOne = [];

				$qianz = 'REPORT_C_' . $wheret . '_' . $value['channel_id'] . $operators[$i]['host'] . '_';

				$sms_all = $redis->get($qianz . 'PT');
				// trace($value['channel_id'].'的总数：'.$sms_all);
				if($sms_all)
				{
					$sms_success = $redis->get($qianz . 'PS');
					if(!$sms_success){
						$sms_success = 0;
					}
					$sms_faild = $redis->get($qianz . 'PF');
					if(!$sms_faild){
						$sms_faild = 0;
					}
					$amt_sms_success = $redis->get($qianz . 'PM');
					if(!$amt_sms_success){
						$amt_sms_success = 0;
					}

					$dataOne['company_name']    = $value['channel_id'] . '/' . $value['company_name'];
			        $dataOne['cid']             = $value['channel_id'];
			        $dataOne['sms_all']         = $sms_all;
			        $dataOne['sms_success']     = $sms_success;
			        $dataOne['sms_faild']       = $sms_faild;
			        $dataOne['sms_sending']     = $sms_all - ( $sms_success + $sms_faild );
			        $dataOne['amt_sms_success'] = $amt_sms_success/1000;

			        //百分比
					$dataOne['sms_success100'] = round(($dataOne['sms_success']/$dataOne['sms_all'])*100,3) . '%';
					$dataOne['sms_faild100']   = round(($dataOne['sms_faild']/$dataOne['sms_all'])*100,3) . '%';
					$dataOne['sms_sending100'] = round(($dataOne['sms_sending']/$dataOne['sms_all'])*100,3) . '%';

					$dataOne['operator'] = $operators[$i]['name'];

					//加入汇总麾下
			        $chaneelsms['data'][] = $dataOne;

			        //渠道汇总
			        $chaneelsms['sms_all']         += $dataOne['sms_all'];
			        $chaneelsms['sms_success']     += $dataOne['sms_success'];
			        $chaneelsms['sms_faild']       += $dataOne['sms_faild'];
			        $chaneelsms['sms_sending']     += $dataOne['sms_sending'];
			        $chaneelsms['amt_sms_success'] += $dataOne['amt_sms_success'];
			        //总汇总
			        $allone['sms_all']         += $dataOne['sms_all'];
			        $allone['sms_success']     += $dataOne['sms_success'];
			        $allone['sms_faild']       += $dataOne['sms_faild'];
			        $allone['sms_sending']     += $dataOne['sms_sending'];
			        $allone['amt_sms_success'] += $dataOne['amt_sms_success'];
				}
			}

			//渠道汇总
			if($chaneelsms['sms_all'] > 0)
			{
				//汇总百分比
				$chaneelsms['sms_success100']  = round(($chaneelsms['sms_success']/$chaneelsms['sms_all'])*100,3) . '%';
				$chaneelsms['sms_faild100']    = round(($chaneelsms['sms_faild']/$chaneelsms['sms_all'])*100,3) . '%';
				$chaneelsms['sms_sending100']  = round(($chaneelsms['sms_sending']/$chaneelsms['sms_all'])*100,3) . '%';
				$chaneelsms['amt_sms_success'] = round($chaneelsms['amt_sms_success'],3);
				$dataall[] = $chaneelsms;
			}
		}	

		$redis->close();

		//输出的数据
		$returnData = [];

		if(count($dataall) > 0)
		{
			//排序
			$smsSuccessList = array_column($dataall,'sms_success');
			array_multisort($smsSuccessList,SORT_DESC,$dataall);
			
			// 把运营商放出来
			foreach ($dataall as $kd => $vd) {
				$data = $vd['data'];
				unset($vd['data']);

				$returnData[] = $vd;

				foreach ($data as $k => $v) {
					$returnData[] = $v;
				}
			}
			unset($dataall);

			//汇总百分比
			$allone['sms_success100']  = round(($allone['sms_success']/$allone['sms_all'])*100,3) . '%';
			$allone['sms_faild100']    = round(($allone['sms_faild']/$allone['sms_all'])*100,3) . '%';
			$allone['sms_sending100']  = round(($allone['sms_sending']/$allone['sms_all'])*100,3) . '%';
			$allone['amt_sms_success'] = round($allone['amt_sms_success'],3);
			array_unshift($returnData, $allone);
		}

		return $returnData;
	}

	/**
	 * 统计渠道发送量
	 * @Author   lf
	 * @DateTime 2019-01-04T13:59:49+0800
	 * @return   [type]                   [description]
	 */
	public function findReportCid($cid,$wheret)
	{
		$dataall = [];

		$wheret = str_replace("-","",$wheret);

		$channel = Db::table('bus_channel_name')->where('cid',$cid)->field(true)->find();
		$dataall['company_name']    = $channel['cname'];
        $dataall['cid']             = $channel['cid'];
        $dataall['sms_all']         = 0;
        $dataall['sms_success']     = 0;
        $dataall['sms_faild']       = 0;
        $dataall['sms_sending']     = 0;
        $dataall['amt_sms_success'] = 0;

		//获取配置
		$config = Config::get('ZD_REDIS_CONFIG');

		//连接redis
		$redis = redisCurl($config['IP'],$config['PORT'],$config['PASSWORD'],6);

		//分运营商
		$operators = [
			['name' => '移动' , 'host' => '_10086']
			,['name' => '联通' , 'host' => '_10010']
			,['name' => '电信' , 'host' => '_10000']
			,['name' => '三网' , 'host' => '_90001']
			,['name' => '三网' , 'host' => '']
		];

		for ($i=0; $i < count($operators); $i++) { 
			//指定前缀
			$qianz = 'REPORT_C_' . $wheret . '_' . $channel['cid'] . $operators[$i]['host'] . '_';

			$sms_all = $redis->get($qianz . 'PT');
			if(!$sms_all){
				$sms_all = 0;
			}
			//获取成功数
			$sms_success = $redis->get($qianz . 'PS');
			if(!$sms_success){
				$sms_success = 0;
			}
			//失败数
			$sms_faild = $redis->get($qianz . 'PF');
			if(!$sms_faild){
				$sms_faild = 0;
			}
			//总金额
			$amt_sms_success = $redis->get($qianz . 'PM');
			if(!$amt_sms_success){
				$amt_sms_success = 0;
			}

			// $dataall['company_name']    = $channel['cname'];
	  //       $dataall['cid']             = $channel['cid'];
	        $dataall['sms_all']         += $sms_all;
	        $dataall['sms_success']     += $sms_success;
	        $dataall['sms_faild']       += $sms_faild;
	        $dataall['sms_sending']     += $sms_all - ( $sms_success + $sms_faild );
        	$dataall['amt_sms_success'] += $amt_sms_success/1000;
		}
		

        return $dataall;
	}


	/**
	 * 月报列表
	 * @Author   lf
	 * @DateTime 2019-10-16T10:51:02+0800
	 * @return   [type]                   [description]array_unshift
	 */
	public function getOrderReportYueArray($request)
	{
		$bus_order_report = Db::table('bus_order_report')
		->field('bus_order_report.channel_id,bus_order_report.isp_host,bus_channel.company_name,sum(sms_all) sms_all,sum(sms_success) sms_success,sum(sms_faild) sms_faild,sum(sms_sending) sms_sending,sum(amt_sms_success) amt_sms_success')
		->join('bus_channel','bus_order_report.channel_id=bus_channel.channel_id','LEFT')
		->where("date_format(bus_order_report.create_time,'%Y-%m')='{$request['seletime']}'");

		if(session('user_type') == 'clerk')
		{
			$bus_order_report = $bus_order_report
			->join('bus_user_and_channel uc','uc.channel_id=bus_order_report.channel_id and user_id = '.session('user_id'));
			// ->where('bus_order_report.channel_id');
		}
		if($request['cha']){
			$bus_order_report = $bus_order_report->where('bus_order_report.channel_id',$request['cha']);
		}
		$bus_order_report = $bus_order_report->group('bus_order_report.channel_id,bus_order_report.isp_host')
		->order('amt_sms_success desc ,bus_order_report.id desc')
		->select();

		$add['company_name'] = '汇总';
		$add['sms_all']         = 0;
		$add['sms_success']     = 0;
		$add['sms_faild']       = 0;
		$add['sms_sending']     = 0;
		$add['amt_sms_success'] = 0;

		//把数据装进去
		$longdata = [];
		foreach ($bus_order_report as $key => $value) {
			$bus_order_report[$key]['company_name'] = $value['channel_id'].'/'.$value['company_name'];
			$bus_order_report[$key]['cid'] = $value['channel_id'];

			if(!$value['isp_host'])
			{
				$bus_order_report[$key]['isp_host'] = '三网';
			}

			$longdata[$value['channel_id'] . 'a'][] = $bus_order_report[$key];
			//汇总
			$add['sms_all']         = bcadd($add['sms_all'],$bus_order_report[$key]['sms_all']);
			$add['sms_success']     = bcadd($add['sms_success'],$bus_order_report[$key]['sms_success']);
			$add['sms_faild']       = bcadd($add['sms_faild'],$bus_order_report[$key]['sms_faild']);
			$add['sms_sending']     = bcadd($add['sms_sending'],$bus_order_report[$key]['sms_sending']);
			$add['amt_sms_success'] = bcadd($add['amt_sms_success'],$bus_order_report[$key]['amt_sms_success'],5);
		}

		$bus_order_report = [];

		// for ($i=0; $i < count($longdata); $i++) { 
		foreach ($longdata as $key => $value) {
			
			$huiz['cid'] = '';
			$huiz['company_name'] = '';
			$huiz['isp_host'] = '汇总';
			$huiz['sms_all']         = 0;
			$huiz['sms_success']     = 0;
			$huiz['sms_faild']       = 0;
			$huiz['sms_sending']     = 0;
			$huiz['amt_sms_success'] = 0;

			for ($j=0; $j < count($value); $j++) { 
				//添加数据
				// $bus_order_report[] = $longdata[$i][$j];
				//汇总
				$huiz['cid'] = $value[$j]['cid'];
				$huiz['company_name'] = $value[$j]['company_name'];
				$huiz['sms_all']         = bcadd($huiz['sms_all'],$value[$j]['sms_all']);
				$huiz['sms_success']     = bcadd($huiz['sms_success'],$value[$j]['sms_success']);
				$huiz['sms_faild']       = bcadd($huiz['sms_faild'],$value[$j]['sms_faild']);
				$huiz['sms_sending']     = bcadd($huiz['sms_sending'],$value[$j]['sms_sending']);
				$huiz['amt_sms_success'] = bcadd($huiz['amt_sms_success'],$value[$j]['amt_sms_success'],5);
			}
			//添加汇总
			$value[] = $huiz;

			//金额排序
			$last_names = array_column($value,'amt_sms_success');
			array_multisort($last_names,SORT_DESC,$value);

			$bus_order_report = array_merge_recursive($bus_order_report,$value);
		}


		//汇总添加到第一项
		array_unshift($bus_order_report,$add);

		return $bus_order_report;
	}

	/**
	 * 单渠道月报表
	 * @Author   lf
	 * @DateTime 2019-10-16T11:25:16+0800
	 * @param    [type]                   $request [description]
	 * @return   [type]                            [description]
	 */
	public function getOrderReportYueCidArray($request)
	{
		$bus_order_report = Db::table('bus_order_report')
		->field('bus_channel.channel_id
			,bus_channel.company_name
			,bus_order_report.isp_host
			,IFNULL(sum(sms_all),0) sms_all
			,IFNULL(sum(sms_success),0) sms_success
			,IFNULL(sum(sms_faild),0) sms_faild
			,IFNULL(sum(sms_sending),0) sms_sending
			,IFNULL(sum(amt_sms_success),0) amt_sms_success'
			)
		->join('bus_channel','bus_order_report.channel_id=bus_channel.channel_id')
		->where("date_format(bus_order_report.create_time,'%Y-%m')='{$request['seletime']}'")
		->where('bus_order_report.channel_id',$request['cid'])
		->order('bus_order_report.id desc')
		->find();

		// $returndata['channel_id'] = 0;
  //       $returndata['company_name'] = '';
     //    $returndata['sms_all'] = 0;
    	// $returndata['sms_success'] = 0;
    	// $returndata['sms_faild'] = 0;
    	// $returndata['sms_sending'] = 0;
    	// $returndata['amt_sms_success'] = 0;
     //    foreach ($bus_order_report as $key => $value) {
     //    	$returndata['channel_id'] = $value['channel_id'];
     //    	$returndata['company_name'] = $value['company_name'];
     //    	$returndata['sms_all'] += $value['sms_all'];
     //    	$returndata['sms_success'] += $value['sms_success'];
     //    	$returndata['sms_faild'] += $value['sms_faild'];
     //    	$returndata['sms_sending'] += $value['sms_sending'];
     //    	$returndata['amt_sms_success'] += $value['amt_sms_success'];
     //    }
		return $bus_order_report;
	}
}